#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2020/4/13 0013 21:45
# @Author  : liangshuo
# @File    : 02-db_class.py
# @Software : PyCharm
import MySQLdb


class DB(object):

    def __init__(self, db_config):
        self.db_config = db_config
        self._connect = None
        self._cursor = self.__init_connect()

    def __init_connect(self):
        try:
            self._connect = MySQLdb.connect(
                host=self.db_config.get('host'),
                user=self.db_config.get('user'),
                password=self.db_config.get('password'),
                database=self.db_config.get('database'),
                port=self.db_config.get('port'),
                compress=1,
                charset="utf8"
            )
            self._cursor = self._connect.cursor()
        except Exception as e:
            print('Exception : %s' % str(e))
        return self._cursor

    # todo 对结果进行处理
    def getAll(self, sql, param=None):
        """执行查询，并取出所有结果集"""
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchall()
        else:
            result = False
        return result

    def execute(self, sql, operation='query', *sql_args):
        try:
            self._cursor.execute(sql, sql_args)
            self._connect.commit()
        except Exception as e:
            self._connect.rollback()

        # update,delete,create返回count
        if operation != 'query':
            rowcount = self._cursor.rowcount   # 影响的行数
            return rowcount

        # select 返回res
        res = self._cursor.fetchall()
        if len(res) == 0 or res is None:
            return None
        if len(res) == 1:
            if len(res[0]) == 1:
                return res[0][0]
            return res[0]
        if len(res[0]) == 1:
            tmp_list = []
            for e in res:
                tmp_list.append(e[0])
            return tmp_list
        return res

    def __del__(self):
        try:
            if self._connect is None or self._cursor is None:
                pass
            else:
                self._cursor.close()
                self._connect.close()
        except Exception as e:
            print('db.py----DB.__del__()---Exception : %s' % str(e))


if __name__ == '__main__':
    SALT_DATABASE = {
        'database': 'goms',
        'user': 'salt',
        'password': 'Giantsre@2019',
        'host': '192.168.23.21',
        'port': 3306,
    }
    zabbix_db = DB(db_config=SALT_DATABASE)
    sql = "select count(*) from salt_returns"
    jids = ['20200413133025345887', '20200413133025345887']
    sql = "select * from salt_returns where jid in (%s);" % (str(jids)[1:-1])
    ret = zabbix_db.getAll(sql=sql)
    print(ret)
    ret = zabbix_db.execute(sql)
    print(ret)
